import json
import pandas as pd
from jbar import bar
from sqlalchemy import create_engine, text
import re
import os
import sys
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
# 读取 .env 文件
import dotenv
from config import create_db_engine, load_env_hsp_abbr
# 创建数据库引擎
engine = create_db_engine()
dotenv.load_dotenv()
# 从 common.rule_flatten_handle 中 导入 flatten_json, unflatten_json
from common.rule_flatten_handle import flatten_json, unflatten_json

# 动态获取所有以HSPAGGR开头的环境变量
hsp_list = []
hspListRaw = load_env_hsp_abbr()
for hspCode, hspAbbr in hspListRaw:
    hsp_list.append(hspAbbr)

# # 读取 packageList.xlsx
# dfPack = pd.read_excel('packageList.xlsx', dtype='str')
# dictPack = dfPack.set_index('package')['valueRaw'].apply(lambda x:str(x).split(',')).to_dict()

# # dictPack 自我扩张
# for key in dictPack.keys():
#     for v in dictPack[key]:
#         if v in dictPack:
#             dictPack[key].extend(dictPack[v])

# 从 数据库表中加载 rule_package，然后加工成为 dictPack
dictPack = {}
with engine.connect() as conn:
    print("读入 rule_package..")
    result = conn.execute(text("SELECT package, value_raw FROM rule_package"))
    rows = result.fetchall()
    packageData = [{'package': row[0], 'value_raw': row[1]} for row in rows]
    for packageDatum in packageData:
        if packageDatum['package'] not in dictPack:
            dictPack[packageDatum['package']] = []
        dictPack[packageDatum['package']].append(packageDatum['value_raw'])

# # 读取 ruleData_all.json
# with open('ruleData_all.json', 'r', encoding='utf-8') as f:
#     ruleDataAll = json.load(f)

# 从数据库中获取 rule_flatten，然后还原成为 ruleDataAll
with engine.connect() as conn:
    print("读入 rule_flatten..")
    result = conn.execute(text("SELECT rule_id, key, type, value FROM rule_flatten ORDER BY id"))
    rows = result.fetchall()
    flatData = [{'rule_id': row[0], 'key': row[1], 'type': row[2], 'value': row[3]} for row in rows]
    # 还原为 ruleDataAll
    ruleDataAll = unflatten_json(flatData)

# 判断数据库中是否有 rule_apply 表，如果有就删除
with engine.connect() as conn:
    print("删除本地数据库中已存在的 rule_apply 表...")
    conn.execute(text("""
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE rule_apply';
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE != -942 THEN
                    RAISE;
                END IF;
        END;
    """))
    conn.commit()

# 创建 rule_apply表，用ORACLE语句
with engine.connect() as conn:
    print("创建 rule_apply 表...")
    conn.execute(text("""
        CREATE TABLE rule_apply (
            hsp_abbr VARCHAR2(255 CHAR),
            rule_id VARCHAR2(50 CHAR),
            key VARCHAR2(1000 CHAR),
            type VARCHAR2(50 CHAR),
            value VARCHAR2(4000 CHAR),
            sql CLOB
        )
    """))
    conn.commit()

def build_pattern(rawList):
    patterns = []
    for x in rawList:
        if not x:
            continue
        # 将%和_转换为正则
        pattern = re.escape(x).replace(r'%', '.*').replace(r'_', '.')
        # 添加完整匹配边界
        pattern = '^' + pattern + '$'
        patterns.append(pattern)
    return '|'.join(patterns) if patterns else '^$'  # 如果patterns为空，返回永不匹配的模式

# 输入一个字符串列表，将其解包的函数
def unpack(packList, conn, hsp_abbr, rawType='icd'):
    """
    解包函数，将 packList 中的每个字符串根据 dictPack 的内容进行解包
    :param packList: 包含要解包的字符串列表
    :param conn: 数据库连接
    :param hsp_abbr: 医院简称
    :param rawType: 解包类型，'icd' 或 'item'
    :return: 解包后的数据列表
    """
    # 对packList中的每个字符串，首先判断是否在dictPack当中，如果是，将dictPack的valueRaw添加到结果列表中，形成rawList
    rawList = []
    for item in packList:
        if item in dictPack:
            rawList.extend(dictPack[item])
        rawList.append(item)

    unpackedHspList = []
    # 合并所有rawList为一个正则表达式
    pattern = build_pattern(rawList)
    if rawType == 'icd':
        df = dfSceneIcd[dfSceneIcd['hsp_abbr'] == hsp_abbr]
        mask = (
            df['icd_code'].str.contains(pattern, case=False, regex=True, na=False) |
            df['icd_name'].str.contains(pattern, case=False, regex=True, na=False) |
            df['icd_hsp_name'].str.contains(pattern, case=False, regex=True, na=False)
        )
        for _, row in df[mask].iterrows():
            unpackedHspList.append({
                'j_code': row['icd_j_code'],
                'j_name': None,
                'codes': row['icd_code'],
                'names': row['icd_name'],
                'med_codes': None,
                'hsp_codes': None,
                'hsp_names': row['icd_hsp_name']
            })
    elif rawType == 'item':
        df = dfSceneItem[dfSceneItem['hsp_abbr'] == hsp_abbr]
        # 如果是药品编码、耗材编码，则连同名字一起解包。判断是否是服务编码：遍历rawList，里面的raw如果有一个是数字开头的，就是服务项目
        isServ = False
        for raw in rawList:
            if not raw:
                continue
            if len(raw)>=1 and raw[0] in ('0','1','2','3','4'):
                isServ = True
                break
        if not isServ:
            mask = (
                df['item_code'].str.contains(pattern, case=False, regex=True, na=False)  |
                df['item_name'].str.contains(pattern, case=False, regex=True, na=False) |
                df['item_med_code'].str.contains(pattern, case=False, regex=True, na=False) |
                df['item_hsp_code'].str.contains(pattern, case=False, regex=True, na=False) |
                df['item_hsp_name'].str.contains(pattern, case=False, regex=True, na=False)
            )
        else:
            mask = (
                df['item_code'].str.contains(pattern, case=False, regex=True, na=False)  |
                df['item_med_code'].str.contains(pattern, case=False, regex=True, na=False) |
                df['item_hsp_code'].str.contains(pattern, case=False, regex=True, na=False)
            )
        # 如果是服务项目，则仅解包项目编码
        for _, row in df[mask].iterrows():
            unpackedHspList.append({
                'j_code': row['item_j_code'],
                'j_name': None,
                'codes': row['item_code'],
                'names': row['item_name'],
                'med_codes': row['item_med_code'],
                'hsp_codes': row['item_hsp_code'],
                'hsp_names': row['item_hsp_name']
            })
    elif rawType == 'item_material':
        df = dfSceneItem[(dfSceneItem['hsp_abbr'] == hsp_abbr)|(dfSceneItem['item_type'] == '卫生材料费')]
        mask = (
            df['item_code'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_name'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_med_code'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_hsp_code'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_hsp_name'].str.contains(pattern, case=False, regex=True, na=False)
        )
        for _, row in df[mask].iterrows():
            unpackedHspList.append({
                'j_code': row['item_j_code'],
                'j_name': None,
                'codes': row['item_code'],
                'names': row['item_name'],
                'med_codes': row['item_med_code'],
                'hsp_codes': row['item_hsp_code'],
                'hsp_names': row['item_hsp_name']
            })
    elif rawType == 'item_drug':
        df = dfSceneItem[(dfSceneItem['hsp_abbr'] == hsp_abbr)|(dfSceneItem['item_type'].isin(['西药费', '中成药费', '中药饮片费']))]
        mask = (
            df['item_code'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_name'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_med_code'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_hsp_code'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_hsp_name'].str.contains(pattern, case=False, regex=True, na=False)
        )
        for _, row in df[mask].iterrows():
            unpackedHspList.append({
                'j_code': row['item_j_code'],
                'j_name': None,
                'codes': row['item_code'],
                'names': row['item_name'],
                'med_codes': row['item_med_code'],
                'hsp_codes': row['item_hsp_code'],
                'hsp_names': row['item_hsp_name']
            })
    elif rawType == 'item_serv':
        df = dfSceneItem[(dfSceneItem['hsp_abbr'] == hsp_abbr)|(~dfSceneItem['item_type'].isin(['西药费', '中成药费', '中药饮片费', '卫生材料费']))]
        mask = (
            df['item_code'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_name'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_med_code'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_hsp_code'].str.contains(pattern, case=False, regex=True, na=False) |
            df['item_hsp_name'].str.contains(pattern, case=False, regex=True, na=False)
        )
        for _, row in df[mask].iterrows():
            unpackedHspList.append({
                'j_code': row['item_j_code'],
                'j_name': None,
                'codes': row['item_code'],
                'names': row['item_name'],
                'med_codes': row['item_med_code'],
                'hsp_codes': row['item_hsp_code'],
                'hsp_names': row['item_hsp_name']
            })
    else:
        raise ValueError("rawType must be either 'icd' or 'item'")
    
    # 返回一个包含每个医院的unpacked数据的列表
    return unpackedHspList


# 创建连接
conn = engine.connect()

# 获取sceneItem的hsp_abbr, item_j_code, item_j_name, item_code, item_name, item_med_code, item_hsp_code, item_hsp_name
sql = f"""
SELECT distinct hsp_abbr, item_j_code, item_code, item_name, item_med_code, item_hsp_code, item_hsp_name, item_type
FROM scene_item_ext
"""
result = conn.execute(text(sql))
# 将查询结果转换为DataFrame
dfSceneItem = pd.DataFrame(result.fetchall(), columns=result.keys())

# 获取sceneIcd的hsp_abbr, icd_code, icd_names, icd_hsp_names
sql = f"""
SELECT distinct hsp_abbr, icd_code, icd_name, icd_hsp_name, icd_j_code
FROM scene_icd
"""
result = conn.execute(text(sql))
# 将查询结果转换为DataFrame
dfSceneIcd = pd.DataFrame(result.fetchall(), columns=result.keys())

# 遍历每个医院
for hsp_abbr in hsp_list:
    print(f"Processing hospital: {hsp_abbr}")

    ruleDataApply = []
    # 遍历ruleDataAll中的每个规则
    for ruleDatum in bar(ruleDataAll):
        # 如果 ruleDatum 中的 isValid != '1' 或者 1 或者 True，则跳过当前规则
        if ruleDatum.get('isValid', '1') not in ('1', 1, True):
            continue

        # debug
        if ruleDatum['ruleId'] == 'I187':
            pass

        ruleActsData = ruleDatum.get('ruleActs', [])
        for datum in ruleActsData:

            # 将ruleDataAll当中，属于收费项目(item)的，或者属于病案编码(icd)的
            # 将这些规则内涵从 raw或者pack 解包为 unpacked

            # 预先假定当前 isRuleActTriggered 是可以触发的
            isRuleActTriggered = True

            # 针对 trigger 进行解包
            # trigger，且trigger_type=='item', raw_type='item'
            if 'trigger' in datum and datum['trigger_type'] in 'item':
                # 针对trigger，按照收费项目解包，形成apply数据
                unpackedData = unpack(datum['trigger'], conn, hsp_abbr, rawType='item')
                datum['trigger_apply'] = unpackedData
            # trigger，且trigger_type=='item_material', raw_type='item'
            elif 'trigger' in datum and datum['trigger_type'] == 'item_material':
                # 针对trigger，按照收费项目解包，形成apply数据
                unpackedData = unpack(datum['trigger'], conn, hsp_abbr, rawType='item_material')
                datum['trigger_apply'] = unpackedData
            # trigger，且trigger_type=='item_drug', raw_type='item_drug'
            elif 'trigger' in datum and datum['trigger_type'] == 'item_drug':
                # 针对trigger，按照收费项目解包，形成apply数据
                unpackedData = unpack(datum['trigger'], conn, hsp_abbr, rawType='item_drug')
                datum['trigger_apply'] = unpackedData
            # 判断 trigger，如果 trigger_apply 是一个空列表，则不能触发，不再进行下面的操作
            if 'trigger_apply' in datum and len(datum['trigger_apply']) == 0:
                isRuleActTriggered = False
                datum['isRuleActTriggered'] = isRuleActTriggered
                continue
            
            # itemextra， raw_type='item'
            if 'itemextra' in datum:
                # 针对itemextra，按照收费项目解包
                unpackedData = unpack(datum['itemextra'], conn, hsp_abbr, rawType='item')
                datum['itemextra_apply'] = unpackedData
            
            # 遍历datum，如果某个键是 itemextra_开头的，则解包，raw_type='item'
            keys = datum.keys()
            itemextra_keys = [key for key in keys if key.startswith('itemextra_') and not key.endswith('_apply')]  
            for key in itemextra_keys:
                unpackedData = unpack(datum[key], conn, hsp_abbr, rawType='item')
                datum[f'{key}_apply'] = unpackedData
        
            # itemscene， raw_type='item'
            if 'itemscene' in datum:
                # 针对itemscene，按照收费项目解包
                unpackedData = unpack(datum['itemscene'], conn, hsp_abbr, rawType='item')
                datum['itemscene_apply'] = unpackedData
            
            # 遍历datum中的filter
            filterData = datum.get('filter', [])
            filterDataApply = []
            for filterDatum in filterData:

                # field是dx或者tx的情况下，解包value，raw_type='icd'
                if filterDatum['field'] in ('dx', 'tx'):
                    # 解包dx的value
                    unpackedData = unpack(filterDatum['value'], conn, hsp_abbr, rawType='icd')
                    filterDatum['value_apply'] = unpackedData
                    # 判断 field 是 dx或tx， 且 operator 是 'with' 的情况下，unpackedData 是否是空列表
                    if filterDatum['operator'] == 'with' and unpackedData == []:
                        # 如果是空列表，则不能触发
                        isRuleActTriggered = False
                
                # field是item的情况下，不存在 is_material键（或者is_material存在但值不是True），解包value，raw_type='item'
                elif filterDatum['field'] == 'item' and (
                    'is_material' not in filterDatum or filterDatum['is_material'] is not True
                ) and ('is_serv' not in filterDatum or filterDatum['is_serv'] is not True):
                    # 解包item的value
                    unpackedData = unpack(filterDatum['value'], conn, hsp_abbr, rawType='item')
                    filterDatum['value_apply'] = unpackedData
                    # 判断 operator 是 'with' 的情况下，unpackedData 是否是空列表
                    if filterDatum['operator'] == 'with' and unpackedData == []:
                        # 如果是空列表，则不能触发
                        isRuleActTriggered = False
                
                # field是item的情况下，is_material键存在且值为True，解包value，raw_type='item_material'
                elif filterDatum['field'] == 'item' and filterDatum.get('is_material', False) is True:
                    # 解包item的value
                    unpackedData = unpack(filterDatum['value'], conn, hsp_abbr, rawType='item_material')
                    filterDatum['value_apply'] = unpackedData
                    # 判断 operator 是 'with' 的情况下，unpackedData 是否是空列表
                    if filterDatum['operator'] == 'with' and unpackedData == []:
                        # 如果是空列表，则不能触发
                        isRuleActTriggered = False
                
                # field是item的情况下，is_serv键存在且值为True，解包value，raw_type='item_serv'
                elif filterDatum['field'] == 'item' and filterDatum.get('is_serv', False) is True:
                    # 解包item的value
                    unpackedData = unpack(filterDatum['value'], conn, hsp_abbr, rawType='item')
                    filterDatum['value_apply'] = unpackedData
                    # 判断 operator 是 'with' 的情况下，unpackedData 是否是空列表
                    if filterDatum['operator'] == 'with' and unpackedData == []:
                        # 如果是空列表，则不能触发
                        isRuleActTriggered = False

                # 当field是item_num, item_day or item_price，而且than=='item'时，解包value，raw_type='item'
                elif filterDatum['field'] in ('item_num', 'item_day', 'item_price', 'item_usage') and filterDatum.get('than', '') == 'item' and 'value' in filterDatum:
                    # 解包item的value
                    unpackedData = unpack(filterDatum['value'], conn, hsp_abbr, rawType='item')
                    filterDatum['value_apply'] = unpackedData
                
                # 当field是item_time，且than=='item'时，解包value，raw_type='item'
                elif filterDatum['field'] == 'item_time' and filterDatum.get('than', '') == 'item':
                    # 解包item的value
                    unpackedData = unpack(filterDatum['value'], conn, hsp_abbr, rawType='item')
                    filterDatum['value_apply'] = unpackedData
                
                # 当field是item_num_ratio，解包numerator和denominator，raw_type='item'
                elif filterDatum['field'] == 'item_num_ratio':
                    # 解包item的numerator
                    unpackedNumerator = unpack(filterDatum['numerator']['value'], conn, hsp_abbr, rawType='item')
                    filterDatum['numerator']['value_apply'] = unpackedNumerator
                    
                    # 解包item的denominator
                    unpackedDenominator = unpack(filterDatum['denominator']['value'], conn, hsp_abbr, rawType='item')
                    filterDatum['denominator']['value_apply'] = unpackedDenominator
                
                # 当field是A8，解包value
                elif filterDatum['field'] == 'A8':
                    # 解包item的value
                    unpackedValue = unpack(filterDatum['value'], conn, hsp_abbr, rawType='item')
                    filterDatum['value_apply'] = unpackedValue
                
                # 当field是C21，解包value_a, value_b, value_c
                elif filterDatum['field'] == 'C21':
                    # 解包item的value_a, value_b, value_c
                    unpackedValue = unpack(filterDatum['value_a'], conn, hsp_abbr, rawType='item')
                    filterDatum['value_a_apply'] = unpackedValue
                    unpackedValue = unpack(filterDatum['value_b'], conn, hsp_abbr, rawType='item')
                    filterDatum['value_b_apply'] = unpackedValue
                    unpackedValue = unpack(filterDatum['value_c'], conn, hsp_abbr, rawType='item')
                    filterDatum['value_c_apply'] = unpackedValue
                
                # 当field是A542，解包 value_a, value_b
                elif filterDatum['field'] == 'A542':
                    # 解包item的value_a, value_b
                    unpackedValueA = unpack(filterDatum['value_a'], conn, hsp_abbr, rawType='item')
                    filterDatum['value_a_apply'] = unpackedValueA
                    unpackedValueB = unpack(filterDatum['value_b'], conn, hsp_abbr, rawType='item')
                    filterDatum['value_b_apply'] = unpackedValueB
            
            datum['isRuleActTriggered'] = isRuleActTriggered

        # 判断当前rule能不能触发，通过判断ruleActsData当中是否有一个 isRuleActTriggered 是 True 的 ruleAct
        isTriggered = False
        for ruleAct in ruleActsData:
            if ruleAct['isRuleActTriggered']:
                isTriggered = True
                break
        
        # 如果可以触发，则将当前的ruleDatum添加到ruleDataApply中，注意，ruleDatum的ruleActs只能包含那些isRuleActTriggered为True的ruleAct
        if isTriggered:
            ruleDatum['ruleActs'] = [ruleAct for ruleAct in ruleActsData if ruleAct['isRuleActTriggered']]
            ruleDataApply.append(ruleDatum)

    # 将解包后的数据写入到ruleData_apply.json
    with open(f'ruleData_apply_{hsp_abbr}.json', 'w', encoding='utf-8') as f:
        json.dump(ruleDataApply, f, ensure_ascii=False, indent=4)
    
    # 将当期的ruleDataApply表扁平化
    print("扁平化 ruleDataApply，并且插入到数据库...")
    ruleFlatTupleApply = flatten_json(ruleDataApply)
    ruleFlatApply = [dict(zip(['rule_id', 'key', 'type', 'value'], item)) for item in ruleFlatTupleApply]
    # 将扁平化后的ruleFlatApply，额外添加一个hsp_abbr字段
    for row in ruleFlatApply:
        row['hsp_abbr'] = hsp_abbr

    # 插入到rule_apply表
    dfRuleFlatApply = pd.DataFrame(ruleFlatApply)
    dfRuleFlatApply.to_sql('rule_apply', conn, if_exists='append', index=False)
    conn.commit()
    
    # 输出一下，这家医院能够触发多少条规则
    print(f"{hsp_abbr} can trigger {len(ruleDataApply)} rules.")

# 关闭连接
conn.close()